﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using HZ.MBSM.DBUtility;
using System.ComponentModel;
using System.Drawing;
using System.Web.SessionState;
using HZ.MBSM.Model;
public partial class Agency_excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
         System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
        _AccountType.Add(HZ.MBSM.DAL.AccountType.enumAttorney);
        if (HZ.MBSM.BLL.Login.CheckAssert(_AccountType))
        {
        }
    }
    protected void daoru_Click(object sender, EventArgs e)
    {
       bool Leadsuccess=SaveExculs();
       if (Leadsuccess)
       {
           exceldaoru();
       }
    }
    private bool SaveExculs()  //上传Excul
    {
        string zixingcaigou = "zixingcaigou.xls";
        string LoadFileName = "";
        Boolean Useful = false;
        string fileName = "";
        string fileExtension = "";
        ///'遍历File表单元素
        HttpFileCollection files = HttpContext.Current.Request.Files;      
        
        System.Text.StringBuilder strMsg = new System.Text.StringBuilder();
        strMsg.Append("上传的文件分别是：<hr color=red>");
        try
        {
            for (int iFile = 0; iFile < files.Count; iFile++)
            {
              

                HttpPostedFile postedFile = files[iFile];
               
                fileName = System.IO.Path.GetFileName(postedFile.FileName);
                fileExtension = System.IO.Path.GetExtension(fileName);
                if (fileExtension.ToUpper() == ".XLS")
                {
                    Useful = true;
                }
                else
                {
                    Useful = false;
                }
                if (fileName != "" && Useful)
                {
                    LoadFileName = fileName;
                    strMsg.Append("上传的文件类型：" + postedFile.ContentType.ToString() + "<br>");
                    strMsg.Append("客户端文件地址：" + postedFile.FileName + "<br>");
                    strMsg.Append("上传文件的文件名：" + fileName + "<br>");
                    strMsg.Append("上传文件的扩展名：" + fileExtension + "<br><hr>");
                    ///'可根据扩展名字的不同保存到不同的文件夹

                    ///注意：可能要修改你的文件夹的匿名写入权限。

                    postedFile.SaveAs(System.Web.HttpContext.Current.Request.MapPath("../UploadFile/") + zixingcaigou);

                }
            }
            if (fileName != "" && Useful)
            {
                // sql = "update project_bulletin set DownUrl='" + LoadFileName + "' where projectid=" + projectid;

                this.strStatus.Text = strMsg.ToString();
                return true;
               
               
            }
            else
            {
                if (fileName == "")
                {
                    this.strStatus.Text = "";
                    return false;
                   
                    
                }
                else
                {
                    this.strStatus.Text = "只能上传文件格式(.xls)!";
                    return false;
                }

            }
        }
        catch (System.Exception Ex)
        {
            this.strStatus.Text = Ex.Message;
            return false;

           
        }

    }


    private void exceldaoru()
    {
        string _strTableExcul = "zixingcaigou";
        string _strTableExcul12 = "zixingcaigou.xls";

        Db db = new Db();
        db.Command(" if exists (select 1  from  sysobjects  where  id = object_id('" + _strTableExcul + "')  and   type = 'U')  drop table " + _strTableExcul + " ");
        //db.Command(" SELECT * into " + _strTableExcul + " FROM opendatasource('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;DATABASE=" + System.Web.HttpContext.Current.Request.MapPath("../UploadFile/") + _strTableExcul12 + ";persist   security   info=false',[Sheet1$])");
       // ImportFromExcel(System.Web.HttpContext.Current.Request.MapPath("../UploadFile/") + _strTableExcul12);
        //db.Command(" SELECT *  FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;DATABASE=\\10.9.9.111\\" + System.Web.HttpContext.Current.Request.MapPath("../UploadFile/") + _strTableExcul12 + "',[Sheet1$])");
        //删除空行
        db.Command(" delete " + _strTableExcul + " where 药品流水号 is null or (药品流水号='')");
        db.Close();
        Response.Write("<script>alert('导入成功')</script>");
        Button1.Visible = true;

    }

    #region 读写EXCEL文件
    /// <summary>
    /// 读取Excel数据到DataSet中
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public DataSet ImportFromExcel(string filePath)
    {
        DataSet ds = new DataSet();Db db = new Db();
        try
        {  
            string connString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

          //  DataTable table = MedicineSystem.Common.DataBase.OleDbOperator.GetExcelTables(connString);
            DataTable table = db.DataTable(connString);
            if (table == null || table.Rows.Count <= 0)
            {
                return null;
            }
            //if (table.Rows.Count > 5)
            //{
            //    throw new Exception("数据太多 "+table.Rows.Count.ToString());
            //}
            //foreach (DataRow dr in table.Rows)
            if (table.Rows.Count > 0)
            {
               
                DataRow dr = table.Rows[0];
                string cmdText = "select * from [" + dr["TABLE_NAME"].ToString() + "]";
                DataTable dt = db.DataTable(cmdText);
                dt.TableName = dr["TABLE_NAME"].ToString();
                if (dt.Rows.Count > 0)
                {//ds.Tables.Add(dt);
                }
              
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        db.Close();
        return ds;

    }
    #endregion
}
